import pymysql
import datetime
import yagmail
import pandas as pd


def task_to_db(script_name, func_name, status=0, args="void"):
    db_config = {
        "charset": "utf8",
        "host": "192.168.0.114",
        "port": 3307,
        "user": "admin",
        "passwd": "123456"
    }
    conn = pymysql.connect(db="task", **db_config)
    cur = conn.cursor()
    today = datetime.date.today()
    sql = f"insert into crypto_quant(`script_name`, `func_name`, `args`, `date`, `status`) values ('{script_name}', '{func_name}', '{args}', '{today}', '{status}');"
    cur.execute(sql)
    conn.commit()
    cur.close()
    conn.close()


def send_error_to_email(script_name, func_name, message):
    sender = {"user": 'tianzhiguanlizixun@163.com', "password": 'VMUXBIXRSTMBJYDW', "host": 'smtp.163.com'}
    yag = yagmail.SMTP(sender['user'], sender['password'], host=sender['host'])
    yag.send(to=['zhanghang0427@163.com','zhanghang0427@gmail.com'], subject=f"{script_name},{func_name}", contents=f"{message}")


def read_sql(sql, db_name):
    db_config = {
        "charset": "utf8",
        "host": "192.168.0.114",
        "port": 3307,
        "user": "admin",
        "passwd": "123456"
    }
    conn = pymysql.connect(db=db_name, **db_config)
    return pd.read_sql(sql, conn)


def check_completion_status_of_tasks(project_name='crypto_quant', date= datetime.date.today()):
    sql = f"select script_name, func_name, args from tasks_detail where project_name = '{project_name}' "
    df1 = read_sql(sql, db_name="task")

    set1 = set(zip(df1['script_name'], df1['func_name'], df1['args']))
    # print(set1)

    sql = f"""select script_name, func_name, args from (

    SELECT script_name, func_name, args, sum(`status`) as sum_status FROM `{project_name}` where date = '{date}' GROUP BY script_name, func_name, args

    ) as t where t.sum_status >= 1;"""
    df2 = read_sql(sql, db_name="task")

    set2 = set(zip(df2['script_name'], df2['func_name'], df2['args']))
    fail_execute_task = []
    unexpected_execute_task = []
    for x in (set1 - set2):
        fail_execute_task.append(x)
    for x in (set2 - set1):
        unexpected_execute_task.append(x)
    send_error_to_email("task_monitor", "check_completion_status_of_tasks",
                        f"fail_execute_task:{fail_execute_task},unexpected_execute_task:{unexpected_execute_task}")


if __name__ == '__main__':
    # task_to_db("send_report", "send_report", "void")
    # task_to_db("send_report2", "send_report", 1)
    check_completion_status_of_tasks()
